"""
检查数据库表结构
"""
import sys
import os

# 添加项目根目录到 Python 路径
sys.path.insert(0, os.path.abspath(os.path.join(os.path.dirname(__file__), '..')))

from sqlalchemy import create_engine, inspect, text
from app.core.config import settings

def check_database():
    """检查数据库表结构"""
    print("=" * 80)
    print("🔍 数据库结构检查")
    print("=" * 80)
    print(f"数据库: {settings.DATABASE_URL.split('@')[1] if '@' in settings.DATABASE_URL else settings.DATABASE_URL}")
    print()
    
    engine = create_engine(settings.DATABASE_URL)
    inspector = inspect(engine)
    
    # 期望的表
    expected_tables = [
        'users',
        'categories', 
        'knowledge_bases',
        'documents',
        'document_shares',
        'chat_sessions',
        'chat_messages'
    ]
    
    # 获取实际存在的表
    actual_tables = inspector.get_table_names()
    
    print(f"✅ 找到 {len(actual_tables)} 个表:")
    print("-" * 80)
    
    for table in expected_tables:
        if table in actual_tables:
            print(f"  ✓ {table}")
            # 获取列信息
            columns = inspector.get_columns(table)
            print(f"    列数: {len(columns)}")
            for col in columns[:5]:  # 只显示前5列
                print(f"      - {col['name']}: {col['type']}")
            if len(columns) > 5:
                print(f"      ... 还有 {len(columns) - 5} 列")
        else:
            print(f"  ✗ {table} (缺失)")
        print()
    
    # 检查额外的表
    extra_tables = set(actual_tables) - set(expected_tables)
    if extra_tables:
        print(f"⚠️  发现额外的表: {', '.join(extra_tables)}")
        print()
    
    # 检查 users 表的详细结构
    if 'users' in actual_tables:
        print("=" * 80)
        print("👤 Users 表详细结构:")
        print("-" * 80)
        columns = inspector.get_columns('users')
        for col in columns:
            nullable = "NULL" if col['nullable'] else "NOT NULL"
            default = f"DEFAULT {col['default']}" if col.get('default') else ""
            print(f"  {col['name']:<20} {str(col['type']):<20} {nullable:<10} {default}")
        
        # 检查用户数据
        with engine.connect() as conn:
            result = conn.execute(text("SELECT COUNT(*) as count FROM users"))
            user_count = result.fetchone()[0]
            print(f"\n  📊 用户总数: {user_count}")
            
            if user_count > 0:
                result = conn.execute(text("SELECT id, username, role, is_active FROM users LIMIT 5"))
                print(f"\n  前 {min(5, user_count)} 个用户:")
                for row in result:
                    print(f"    ID: {row[0]}, 用户名: {row[1]}, 角色: {row[2]}, 活跃: {row[3]}")
    
    # 检查 documents 表的详细结构
    if 'documents' in actual_tables:
        print("\n" + "=" * 80)
        print("📄 Documents 表详细结构:")
        print("-" * 80)
        columns = inspector.get_columns('documents')
        for col in columns:
            nullable = "NULL" if col['nullable'] else "NOT NULL"
            print(f"  {col['name']:<25} {str(col['type']):<30} {nullable}")
        
        # 检查文档数据
        with engine.connect() as conn:
            result = conn.execute(text("SELECT COUNT(*) as count FROM documents"))
            doc_count = result.fetchone()[0]
            print(f"\n  📊 文档总数: {doc_count}")
    
    print("\n" + "=" * 80)
    print("✅ 数据库检查完成！")
    print("=" * 80)

if __name__ == "__main__":
    try:
        check_database()
    except Exception as e:
        print(f"\n❌ 检查失败: {e}")
        import traceback
        traceback.print_exc()
        sys.exit(1)

